<?php
class Querys
{
    // CONSTANTS
    public static $T_CPES = "cpe";
    public static $T_NOISE = "noise_data";
    public static $T_WIFI = "wifi_data";
    //<---wifi_hour and noise_hour -- armazenam os dados wifi e a media do noise hora a hora
    public static $T_NOISE_HOUR = "noise_hour";
    public static $T_NOISE_HOUR_ID = "noise_hour_id";
    public static $T_NOISE_HOUR_TIME = "time";
    public static $T_NOISE_HOUR_CPE_ID = "cpe_id";
    public static $T_NOISE_HOUR_CHANNEL = "average";
    public static $T_NOISE_HOUR_DEVIATION = "deviation";
    public static $T_NOISE_HOUR_MAX_NOISE= "max_noise";
    //--->
    public static $T_WIFI_HOUR = "wifi_hour";
    public static $T_WIFI_HOUR_ID ="wifi_hour_id";
    public static $T_WIFI_HOUR_TIME ="time";
    public static $T_WIFI_HOUR_MAC = "mac";
    public static $T_WIFI_HOUR_SSID = "ssid";
    public static $T_WIFI_HOUR_CHANNEL = "channel";
    public static $T_WIFI_HOUR_SIGNAL = "signal";
    public static $T_WIFI_HOUR_SECURITY = "security";
    public static $T_WIFI_HOUR_UNKNOWN = "unknown";
    //--->
    public static $T_ALERTS = "alerts";
    public static $T_SETTINGS = "settings";
    public static $T_ZONAS = "zonas";
       
    public static $F_CPE_ID = "cpe_id";
    public static $F_CPE_MAC = "mac";
    public static $F_CPE_SSID = "ssid";
    public static $F_CPE_ZIP = "zip";
    public static $F_CPE_STREET = "street";
    public static $F_CPE_CITY = "city";
    public static $F_CPE_ACCESS = "access";
    public static $F_CPE_WLAN_CHANNEL = "wlan_channel";
    public static $F_CPE_WLAN_STATUS = "wlan_status";
    public static $F_CPE_WLAN_ENABLE = "wlan_enable";
    public static $F_CPE_WLAN_RADIOENABLE = "wlan_radioenable";
    public static $F_CPE_WLAN_STANDARD = "wlan_standard";
    public static $F_CPE_WLAN_CHANNELMODE = "wlan_channelmode";
    public static $F_CPE_WLAN_SSIDADVERT = "wlan_ssidadvert";


    public static $F_NOISE_ID = "noise_data_id";
    public static $F_NOISE_CPE_ID = "cpe_id";
    public static $F_NOISE_TIME = "time";
    public static $F_NOISE_CHANNEL = "channel";
    public static $F_NOISE_BUSY = "busy";
    public static $F_NOISE_BSS_COUNT = "bss_count";
    public static $F_NOISE_UNKNOWN = "unknown";
    public static $F_NOISE_TOTAL_NOISE = "total_noise";
    public static $F_WIFI_ID = "wifi_data_id";
    public static $F_WIFI_CPE_ID = "cpe_id";
    public static $F_WIFI_TIME = "time";
    public static $F_WIFI_MAC = "mac";
    public static $F_WIFI_SSID = "ssid";
    public static $F_WIFI_CHANNEL = "channel";
    public static $F_WIFI_SIGNAL = "signal";
    public static $F_WIFI_SECURITY = "security";
    public static $F_WIFI_UNKNOWN = "unknown";
    //<---ainda não está implementado na BD
    public static $F_ALERT_ID = "alert_id";
    public static $F_ALERT_TABLE_TYPE = "table_type";
    public static $F_ALERT_TABLE_ID = "table_id";
    public static $F_ALERT_ALERT_TYPE = "alert_type";
    public static $F_ALERT_MESSAGE = "message";
    public static $F_ALERT_DISMISSED = "dismissed";
    public static $F_SETTING_SETTING = "setting";
    public static $F_SETTING_VALUE = "value";
    public static $F_ZONAS_RUAID = "ruaid";
    public static $F_ZONAS_RUA = "rua";
    public static $F_ZONAS_JUNTA = "junta_freguesia";
    public static $F_ZONAS_CIDADE = "cidade";
    public static $F_ZONAS_CP = "cp";
    //------>

    public static function getChannelsQuery ($cpe_id)
    {
        return "select distinct " . self::$F_NOISE_CHANNEL . " " .
                "from " . self::$T_NOISE . " " .
                "where " . self::$F_NOISE_CPE_ID . " = " . $cpe_id . ";";
    }

    public static function getMostRecentNetworksAndMacsQuery ($cpe_id)
    {
        return "select " .
                self::$F_WIFI_SSID . ", " .
                self::$F_WIFI_MAC . " " .
                "from " . self::$T_WIFI . " " .
                "where " . self::$F_WIFI_TIME .
                    " = (" .
                        "select " . self::$F_WIFI_TIME . " " .
                        "from " . self::$T_WIFI . " " .
                        "where " . self::$F_WIFI_CPE_ID . " = " . $cpe_id . " " .
                        "order by " . self::$F_WIFI_TIME . " desc " .
                        "limit 1" .
                    ") " .
                "and " . self::$F_WIFI_CPE_ID . " = " . $cpe_id . ";";
    }

    public static function getAllNetworksInfoQuery ($cpe_id)
    {
        return "set @last_time = (" .
            "select " . self::$F_WIFI_TIME .  " " .
            "from " . self::$T_WIFI . " " .
            "where " . self::$F_WIFI_CPE_ID . " = " . $cpe_id . " " .
            "order by " . self::$F_WIFI_TIME . " desc " .
            "limit 1".
                "); " .
                "select " .
            "normal." . self::$F_WIFI_SSID . " as ssid, ".
            "normal." . self::$F_WIFI_MAC . " as mac, ".
            "normal." . self::$F_WIFI_TIME . " as first, ".
            "if(reverse." . self::$F_WIFI_TIME . " = @last_time, " .
                "null," .
                "reverse." . self::$F_WIFI_TIME .
            ") as last " .
            "from " .
            "(" .
                "select time, ssid, mac " .
                "from " . self::$T_WIFI . " " .
                "where " . self::$F_WIFI_CPE_ID . " = " . $cpe_id . " " .
                "order by " . self::$F_WIFI_TIME . " asc" .
            ") normal, " .
            "(" .
                "select time, ssid, mac " .
                "from " . self::$T_WIFI . " " .
                "where " . self::$F_WIFI_CPE_ID . " = " . $cpe_id . " " .
                "order by " . self::$F_WIFI_TIME . " desc" .
            ") reverse " .
            "where normal." . self::$F_WIFI_SSID . " = reverse." . self::$F_WIFI_SSID . " " .
            "and normal." . self::$F_WIFI_MAC . " = reverse." . self::$F_WIFI_MAC . " " .
            "group by normal." . self::$F_WIFI_SSID . ", normal." . self::$F_WIFI_MAC . ";";
    }

    public static function getNoiseDataHourQuery ($cpe_id, $regex_channel, $start_time, $end_time)
    {
        return "select " .
                "date_format(" . self::$F_NOISE_TIME . ",'%Y-%m-%dT%H:%i:%s') as time, " .
                self::$F_NOISE_CHANNEL . ", " .
                "avg(" . self::$F_NOISE_TOTAL_NOISE . ") as total_noise " .
                "from " . self::$T_NOISE . " " .
                "where " . self::$F_NOISE_CPE_ID . " = " . $cpe_id . " " .
                "and " . self::$F_NOISE_CHANNEL . " regexp '" . $regex_channel . "' " .
                "and " . self::$F_NOISE_TIME  . " >= '" . $start_time . "' " .
                "and " . self::$F_NOISE_TIME  . " <= '" . $end_time . "' " .
                "group by date_format(" . self::$F_NOISE_TIME . ",'%Y-%m-%d %H'), " .
                self::$F_NOISE_CHANNEL . ";";
    }

    // -> Acrescentei aqui
    public static function getNoiseDataMinuteQuery ($cpe_id, $regex_channel, $start_time, $end_time)
    {
        return "select " .
                "date_format(" . self::$F_NOISE_TIME . ",'%Y-%m-%dT%H:%i:%s') as time, " .
                self::$F_NOISE_CHANNEL . ", " .
                "avg(" . self::$F_NOISE_TOTAL_NOISE . ") as total_noise " .
                "from " . self::$T_NOISE . " " .
                "where " . self::$F_NOISE_CPE_ID . " = " . $cpe_id . " " .
                "and " . self::$F_NOISE_CHANNEL . " regexp '" . $regex_channel . "' " .
                "and " . self::$F_NOISE_TIME  . " >= '" . $start_time . "' " .
                "and " . self::$F_NOISE_TIME  . " <= '" . $end_time . "' " .
                "group by date_format(" . self::$F_NOISE_TIME . ",'%Y-%m-%d %H:%i'), " .
                self::$F_NOISE_CHANNEL . ";";
    }

    public static function getAllZipCodesQuery ()
    {
        return "select " .
                self::$F_CPE_ID . ", " .
                self::$F_CPE_ZIP . " " .
                "from " . self::$T_CPES . ";";
    }

    public static function getWifiDataCountHourQuery ($cpe_id, $start_time, $end_time)
    {
        return "select " .
            "date_format(" . self::$F_NOISE_TIME . ",'%Y-%m-%dT%H:%i:%s') as time, " .
            "count(distinct if(". self::$F_WIFI_SSID . " like 'VODA%',". self::$F_WIFI_SSID . ", null)) as Vodafone, " .
            "count(distinct if(". self::$F_WIFI_SSID . " like 'MEO%',". self::$F_WIFI_SSID . ", null)) as MEO, " .
            "count(distinct if(". self::$F_WIFI_SSID . " like 'ZON%',". self::$F_WIFI_SSID . ", null)) as ZON, " .
            "(" .
                "count(distinct ". self::$F_WIFI_SSID . ")-" .
                "count(distinct if(". self::$F_WIFI_SSID . " like 'VODA%',". self::$F_WIFI_SSID . ", null))-" .
                "count(distinct if(". self::$F_WIFI_SSID . " like 'MEO%',". self::$F_WIFI_SSID . ", null))-" .
                "count(distinct if(". self::$F_WIFI_SSID . " like 'ZON%',". self::$F_WIFI_SSID . ", null))" .
            ") as Others " .
            "from " . self::$T_WIFI . " " .
            "where " . self::$F_WIFI_CPE_ID . " = 1 " .
            "and " . self::$F_WIFI_TIME  . " >= '" . $start_time . "' " .
            "and " . self::$F_WIFI_TIME  . " <= '" . $end_time . "' " .
            "group by date_format(" . self::$F_WIFI_TIME . ",'%Y-%m-%d %H');";
    }

    // -> Acrescentei aqui
    public static function getWifiDataCountMinuteQuery ($cpe_id, $start_time, $end_time)
    {
        return "select " .
            "date_format(" . self::$F_NOISE_TIME . ",'%Y-%m-%dT%H:%i:%s') as time, " .
            "count(distinct if(". self::$F_WIFI_SSID . " like 'VODA%',". self::$F_WIFI_SSID . ", null)) as Vodafone, " .
            "count(distinct if(". self::$F_WIFI_SSID . " like 'MEO%',". self::$F_WIFI_SSID . ", null)) as MEO, " .
            "count(distinct if(". self::$F_WIFI_SSID . " like 'ZON%',". self::$F_WIFI_SSID . ", null)) as ZON, " .
            "(" .
                "count(distinct ". self::$F_WIFI_SSID . ")-" .
                "count(distinct if(". self::$F_WIFI_SSID . " like 'VODA%',". self::$F_WIFI_SSID . ", null))-" .
                "count(distinct if(". self::$F_WIFI_SSID . " like 'MEO%',". self::$F_WIFI_SSID . ", null))-" .
                "count(distinct if(". self::$F_WIFI_SSID . " like 'ZON%',". self::$F_WIFI_SSID . ", null))" .
            ") as Others " .
            "from " . self::$T_WIFI . " " .
            "where " . self::$F_WIFI_CPE_ID . " = 1 " .
            "and " . self::$F_WIFI_TIME  . " >= '" . $start_time . "' " .
            "and " . self::$F_WIFI_TIME  . " <= '" . $end_time . "' " .
            "group by date_format(" . self::$F_WIFI_TIME . ",'%Y-%m-%d %H:%i');";
    }

    public static function getWifiDataCountAllByDayQuery ($cpe_id)
    {
        return "select " .
            "date_format(" . self::$F_NOISE_TIME . ",'%Y-%m-%d') as time, " .
            "count(distinct if(". self::$F_WIFI_SSID . " like 'VODA%',". self::$F_WIFI_SSID . ", null)) as Vodafone, " .
            "count(distinct if(". self::$F_WIFI_SSID . " like 'MEO%',". self::$F_WIFI_SSID . ", null)) as MEO, " .
            "count(distinct if(". self::$F_WIFI_SSID . " like 'ZON%',". self::$F_WIFI_SSID . ", null)) as ZON, " .
            "(" .
                "count(distinct ". self::$F_WIFI_SSID . ")-" .
                "count(distinct if(". self::$F_WIFI_SSID . " like 'VODA%',". self::$F_WIFI_SSID . ", null))-" .
                "count(distinct if(". self::$F_WIFI_SSID . " like 'MEO%',". self::$F_WIFI_SSID . ", null))-" .
                "count(distinct if(". self::$F_WIFI_SSID . " like 'ZON%',". self::$F_WIFI_SSID . ", null))" .
            ") as Others " .
            "from " . self::$T_WIFI . " " .
            "where " . self::$F_WIFI_CPE_ID . " = 1 " .
            "group by date_format(" . self::$F_WIFI_TIME . ",'%Y-%m-%d');";
    }

    public static function getNumThresholdAlertsQuery ($cpe_id)
    {
        return "select " .
            "count(*) as num " .
            "from " . self::$T_ALERTS . ", " .
            self::$T_NOISE . " " .
            "where " . self::$F_ALERT_ALERT_TYPE . " = 1 " .
            "and " . self::$F_ALERT_TABLE_ID. " = " . self::$F_NOISE_ID . " " .
            "and " . self::$F_NOISE_CPE_ID . " = " . $cpe_id . " " .
            "and " . self::$F_ALERT_DISMISSED . " = 0;";
    }

    public static function getNoiseDataTimeBoundsQuery ($cpe_id)
    {
        /*
        return "set @first = ".
            "(" .
                "select " .
                self::$F_NOISE_TIME . " " .
                "from " . self::$T_NOISE . " " .
                "where " . self::$F_NOISE_CPE_ID . " = " . $cpe_id . " " .
                "order by " . self::$F_NOISE_TIME . " asc " .
                "limit 1" .
            ");" .
            "set @last = ".
            "(" .
                "select " .
                self::$F_NOISE_TIME . " " .
                "from " . self::$T_NOISE . " " .
                "where " . self::$F_NOISE_CPE_ID . " = " . $cpe_id . " " .
                "order by " . self::$F_NOISE_TIME . " desc " .
                "limit 1" .
            ");" .    
            "select @first as first, @last as last;";
        */
        return "select " .
            self::$T_NOISE . "." . self::$F_NOISE_TIME . " as first, " .
            "Others." . self::$F_NOISE_TIME . " as last " .
            "from " . self::$T_NOISE . "," .
            "(" .
                "select " .
                self::$F_NOISE_TIME . " " .
                "from " . self::$T_NOISE . " " .
                "order by " . self::$F_NOISE_TIME . " desc limit 1" .
            ") Others " .
            "order by " . self::$T_NOISE . "." . self::$F_NOISE_TIME . " asc " .
            "limit 1;";

    }

    public static function getWifiDataTimeBoundsQuery ($cpe_id)
    {
        /*
        return "set @first = ".
            "(" .
                "select " .
                self::$F_WIFI_TIME . " " .
                "from " . self::$T_WIFI . " " .
                "where " . self::$F_WIFI_CPE_ID . " = " . $cpe_id . " " .
                "order by " . self::$F_WIFI_TIME . " asc " .
                "limit 1" .
            ");" .
            "set @last = ".
            "(" .
                "select " .
                self::$F_WIFI_TIME . " " .
                "from " . self::$T_WIFI . " " .
                "where " . self::$F_WIFI_CPE_ID . " = " . $cpe_id . " " .
                "order by " . self::$F_WIFI_TIME . " desc " .
                "limit 1" .
            ");" .    
            "select @first as first, @last as last;";
            */
        return "select " .
            self::$T_WIFI . "." . self::$F_WIFI_TIME . " as first, " .
            "Others." . self::$F_WIFI_TIME . " as last " .
            "from " . self::$T_WIFI . "," .
            "(" .
                "select " .
                self::$F_WIFI_TIME . " " .
                "from " . self::$T_WIFI . " " .
                "order by " . self::$F_WIFI_TIME . " desc limit 1" .
            ") Others " .
            "order by " . self::$T_WIFI . "." . self::$F_WIFI_TIME . " asc " .
            "limit 1;";
    }

    public static function getCPEsByZipQuery ($zip)
    {
        return "select " .
            self::$F_CPE_ID . ", " .
            self::$F_CPE_SSID . " " .
            "from " . self::$T_CPES . " " .
            "where " . self::$F_CPE_ZIP . " like \"" . $zip . "%\"";
    }

    public static function getJuntasByZipQuery ($cp)
    {

        return "select  " .
            "distinct " . self::$F_ZONAS_JUNTA . " " .
            "from " . self::$T_ZONAS . " " .
            "where " . self::$F_ZONAS_CP . " like \"" . $cp . "%\"";
    }

    //Joao Rodrigues - SmartSearch ByZip
    public static function getCPEsZipByZipQuery ($zip)
    {
        return "select " .
            self::$F_CPE_ZIP . ", " .
            self::$F_CPE_SSID . " " .
            "from " . self::$T_CPES . " " .
            "where " . self::$F_CPE_ZIP . " like \"" . $zip . "%\"";
    }

    //Joao Rodrigues - SmartSearch ByMac
    public static function getCPEsMacByMacQuery ($mac)
    {
        return "select " .
            self::$F_CPE_MAC . ", " .
            self::$F_CPE_SSID . " " .
            "from " . self::$T_CPES . " " .
            "where " . self::$F_CPE_MAC . " like \"" . $mac . "%\"";
    }

    //Joao Rodrigues
    public static function getCPEInfoByZipQuery ($zip)
    {
        return "select " .
            self::$F_CPE_ID . ", " .
            self::$F_CPE_MAC . ", " .
            self::$F_CPE_SSID . " " .
            "from " . self::$T_CPES . " " .
            "where " . self::$F_CPE_ZIP . " like \"" . $zip . "%\"";
    }

    //Joao Rodrigues
    public static function getCPEInfoByMacQuery ($mac)
    {
        return "select " .
            self::$F_CPE_ID . ", " .
            self::$F_CPE_MAC . ", " .
            self::$F_CPE_SSID . " " .
            "from " . self::$T_CPES . " " .
            "where " . self::$F_CPE_MAC . " like \"" . $mac . "%\"";
    }

    //Joao Rodrigues
    public static function getAllCitiesQuery()
    {
        return "SELECT DISTINCT " . self::$F_CPE_CITY . " FROM " . self::$T_CPES .";";
    }

    //Joao Rodrigues
    public static function getStreetsByCityQuery ($city)
    {
        return "SELECT " . self::$F_CPE_ZIP . " ," . self::$F_CPE_STREET .
        " from " . self::$T_CPES . 
        " where " . self::$F_CPE_CITY . " like \"" . $city . "\";";
    }

    public static function getAccessByCityQuery ($city)
    {
        return "SELECT " . self::$F_CPE_ZIP . " ," . self::$F_CPE_ACCESS .
        " from " . self::$T_CPES . 
        " where " . self::$F_CPE_CITY . " like \"" . $city . "\" GROUP by access;";
    }

    //Joao Rodrigues
    public static function getCPEsByStreetQuery ($street)
    {
        return "SELECT " . self::$F_CPE_ID . ", " .
            self::$F_CPE_MAC . ", " .
            self::$F_CPE_SSID .
        " from " . self::$T_CPES . 
        " where " . self::$F_CPE_STREET . " like \"" . $street . "\";";
    }

    //Joao Rodrigues
    public static function getCPEFullInfoQuery ($cpe_id)
    {
        return "SELECT " . 
            self::$F_CPE_ID . ", " .
            self::$F_CPE_MAC . ", " .
            self::$F_CPE_SSID . ", " .
            self::$F_CPE_CITY . ", " .
	    self::$F_CPE_ACCESS . ", " .
            self::$F_CPE_STREET . ", " .
            self::$F_CPE_ZIP . ", " .
            self::$F_CPE_WLAN_CHANNEL . ", " .
            self::$F_CPE_WLAN_STATUS . ", " .
            self::$F_CPE_WLAN_ENABLE . ", " .
            self::$F_CPE_WLAN_RADIOENABLE . ", " .
            self::$F_CPE_WLAN_STANDARD . ", " .
            self::$F_CPE_WLAN_CHANNELMODE . ", " .
            self::$F_CPE_WLAN_SSIDADVERT .
        " from " . self::$T_CPES . 
        " where " . self::$F_CPE_ID . " = " . $cpe_id . ";";
    }
}
?>
